﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using DBUtility;

namespace DAL
{
    /// <summary>
    /// 推广系统 推广员信息
    /// </summary>
    public class Spreader
    {
        #region code
        public Spreader()
        { }
        public bool SpreaderAdd(MODEL.Spreader model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id", SqlDbType.VarChar, 20),
                                         new SqlParameter("@username",SqlDbType.VarChar,50),
                                         new SqlParameter("@password",SqlDbType.VarChar,100),
                                         new SqlParameter("@qq",SqlDbType.VarChar,20),
                                         new SqlParameter("@email",SqlDbType.VarChar,100),
                                         new SqlParameter("@tel",SqlDbType.VarChar,50),
                                         new SqlParameter("@bank",SqlDbType.VarChar,50),
                                         new SqlParameter("@bankcode",SqlDbType.VarChar,20),
                                         new SqlParameter("@bankname",SqlDbType.VarChar,20),
                                         new SqlParameter("@logontime",SqlDbType.DateTime),
                                         new SqlParameter("@logonip",SqlDbType.VarChar,15),
                                         new SqlParameter("@questionid",SqlDbType.Int),
                                         new SqlParameter("@answer",SqlDbType.VarChar,50),
                                         new SqlParameter("@stype",SqlDbType.Bit),
                                         new SqlParameter("@state",SqlDbType.Bit),
                                         new SqlParameter("@userid",SqlDbType.VarChar,20),
                                         new SqlParameter("@name",SqlDbType.VarChar,20),
                                         new SqlParameter("@image",SqlDbType.Text),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[1].Value = model.SUSERNAME;
            parameter[2].Value = model.SPASSWORD;
            parameter[3].Value = model.SQQ;
            parameter[4].Value = model.SEMAIL;
            parameter[5].Value = model.STEL;
            parameter[6].Value = model.SBANK;
            parameter[7].Value = model.SBANKCODE;
            parameter[8].Value = model.SBANKNAME;
            parameter[9].Value = model.SLOGONTIME;
            parameter[10].Value = model.SLOGONIP;
            parameter[11].Value = model.SQUESTIONID;
            parameter[12].Value = model.SANSWER;
            parameter[13].Value = model.STYPE;
            parameter[14].Value = model.SSTATE;
            parameter[15].Value = model.SUSERID;
            parameter[16].Value = model.SNAME;
            parameter[17].Value = model.SIMAGE;
            for (int i = 0; i < 18; i++)
            {
                parameter[i].Direction = ParameterDirection.Input;
            }
            parameter[18].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderAdd", parameter);
        }
        public bool SpreaderDel(string spreaderId)
        {
            SqlParameter[] parameter = { new SqlParameter("@id", SqlDbType.VarChar, 20),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = spreaderId;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderDel", parameter);
        }
        public bool SpreaderUpdatePassword(MODEL.Spreader model)
        {
            SqlParameter[] parameter ={new SqlParameter("@id",SqlDbType.VarChar,20),
                                       new SqlParameter("@password",SqlDbType.VarChar,100),
                                       new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SPASSWORD;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderUpdatePassword", parameter);
        }
        public bool SpreaderUpdateInformation(MODEL.Spreader model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.VarChar,20),
                                         new SqlParameter("@qq",SqlDbType.VarChar,20),
                                         new SqlParameter("@email",SqlDbType.VarChar,100),
                                         new SqlParameter("@tel",SqlDbType.VarChar,50),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SQQ;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Value = model.SEMAIL;
            parameter[2].Direction = ParameterDirection.Input;
            parameter[3].Value = model.STEL;
            parameter[3].Direction = ParameterDirection.Input;
            parameter[4].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderUpdateInformation", parameter);
        }
        public bool SpreaderUpdateBank(MODEL.Spreader model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.VarChar,20),
                                         new SqlParameter("@bank",SqlDbType.VarChar,50),
                                         new SqlParameter("@bankcode",SqlDbType.VarChar,20),
                                         new SqlParameter("@bankname",SqlDbType.VarChar,20),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SBANK;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Value = model.SBANKCODE;
            parameter[2].Direction = ParameterDirection.Input;
            parameter[3].Value = model.SBANKNAME;
            parameter[3].Direction = ParameterDirection.Input;
            parameter[4].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderUpdateBank", parameter);
        }
        public bool SpreaderUpdateLogon(MODEL.Spreader model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.VarChar,20),
                                         new SqlParameter("@time",SqlDbType.DateTime),
                                         new SqlParameter("@ip",SqlDbType.VarChar,15),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SLOGONTIME;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Value = model.SLOGONIP;
            parameter[2].Direction = ParameterDirection.Input;
            parameter[3].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderUpdateLogon", parameter);
        }
        public bool SpreaderUpdateQuestion(MODEL.Spreader model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.VarChar,20),
                                         new SqlParameter("@questionid",SqlDbType.Int),
                                         new SqlParameter("@answer",SqlDbType.VarChar,50),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SQUESTIONID;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Value = model.SANSWER;
            parameter[2].Direction = ParameterDirection.Input;
            parameter[3].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderUpdateQuestion", parameter);
        }
        public bool SpreaderLogon(string userName, string passWord)
        {
            SqlParameter[] parameter ={new SqlParameter("@username",SqlDbType.VarChar,50),
                                       new SqlParameter("@password",SqlDbType.VarChar,100),
                                       new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = userName;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = passWord;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaderSelectByUsernameANDPassword", parameter);
        }
        public DataSet SpreadList()
        {
            return DbHelper.RunProcedure("ProSpreaderSelect");
        }
        public DataSet SpreadList(string ID)
        {
            SqlParameter[] parameter = { new SqlParameter("@id", SqlDbType.VarChar, 20) };
            parameter[0].Value = ID;
            parameter[0].Direction = ParameterDirection.Input;
            return DbHelper.RunProcedureS("ProSpreaderSelectByID", parameter);
        }
        public string GetSpreaderID()
        {
            string returnStr = string.Empty;
            string id = DateTime.Now.ToString("yyyyMMdd");
            string sqlStr = "SELECT MAX(SID) FROM G_SPREADER WHERE SID LIKE '" + id + "%'";
            DataSet ds = DbHelper.Query(sqlStr);
            if (ds.Tables[0].Rows[0][0].ToString() == "null" || ds.Tables[0].Rows[0][0].ToString() == null || ds.Tables[0].Rows[0][0].ToString() == "")
            {
                returnStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "001";
            }
            else
            {
                int i = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString().Substring(17, 3));
                returnStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + string.Format("{0:D3}", i+1);
            }
            return returnStr;
        }
        public string GetSpreadIdByName(string userName)
        {
            string returnStr = string.Empty;
            string sqlStr = "SELECT * FROM G_SPREADER WHERE SUSERNAME='" + userName + "'";
            DataSet ds = DbHelper.Query(sqlStr);
            if (ds.Tables[0].Rows.Count > 0)
            {
                returnStr = ds.Tables[0].Rows[0]["Sid"].ToString();
            }
            return returnStr;
        }
        #endregion
    }
    /// <summary>
    /// 推广系统 网吧信息
    /// </summary>
    public class Spreadip
    {
        public Spreadip()
        { }
        public bool SpreadipAdd(MODEL.SpreadIP model)
        {
            SqlParameter[] parameter = { new SqlParameter("@ipname",SqlDbType.VarChar,100),
                                         new SqlParameter("@ipnumber",SqlDbType.VarChar,15),
                                         new SqlParameter("@province",SqlDbType.VarChar,20),
                                         new SqlParameter("@city",SqlDbType.VarChar,50),
                                         new SqlParameter("@county",SqlDbType.VarChar,50),
                                         new SqlParameter("@district",SqlDbType.VarChar,50),
                                         new SqlParameter("@address",SqlDbType.VarChar,200),
                                         new SqlParameter("@spreadid",SqlDbType.VarChar,20),
                                         new SqlParameter("@tel",SqlDbType.VarChar,20),
                                         new SqlParameter("@contact",SqlDbType.VarChar,20),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SIPNAME;
            parameter[1].Value = model.SIPNUMBER;
            parameter[2].Value = model.SPROVINCE;
            parameter[3].Value = model.SCITY;
            parameter[4].Value = model.SCOUNTY;
            parameter[5].Value = model.SDISTRICT;
            parameter[6].Value = model.SADDRESS;
            parameter[7].Value = model.SSPREADID;
            parameter[8].Value = model.STEL;
            parameter[9].Value = model.SCONTACT;
            for (int i = 0; i < 10; i++)
            {
                parameter[i].Direction = ParameterDirection.Input;
            }
            parameter[10].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreadipAdd", parameter);
        }
        public bool SpreadipDel(int ipId)
        {
            SqlParameter[] parameter = { new SqlParameter("@id", SqlDbType.Int),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = ipId;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreadipDel", parameter);
        }
        public bool SpreadipUpdate(MODEL.SpreadIP model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.Int),
                                         new SqlParameter("@ipname",SqlDbType.VarChar,100),
                                         new SqlParameter("@ipnumber",SqlDbType.VarChar,15),
                                         new SqlParameter("@province",SqlDbType.VarChar,20),
                                         new SqlParameter("@city",SqlDbType.VarChar,50),
                                         new SqlParameter("@county",SqlDbType.VarChar,50),
                                         new SqlParameter("@district",SqlDbType.VarChar,50),
                                         new SqlParameter("@address",SqlDbType.VarChar,200),
                                         new SqlParameter("@spreadid",SqlDbType.VarChar,20),
                                         new SqlParameter("@tel",SqlDbType.VarChar,20),
                                         new SqlParameter("@contact",SqlDbType.VarChar,20),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SID;
            parameter[1].Value = model.SIPNAME;
            parameter[2].Value = model.SIPNUMBER;
            parameter[3].Value = model.SPROVINCE;
            parameter[4].Value = model.SCITY;
            parameter[5].Value = model.SCOUNTY;
            parameter[6].Value = model.SDISTRICT;
            parameter[7].Value = model.SADDRESS;
            parameter[8].Value = model.SSPREADID;
            parameter[9].Value = model.STEL;
            parameter[10].Value = model.SCONTACT;
            for (int i = 0; i < 11; i++)
            {
                parameter[i].Direction = ParameterDirection.Input;
            }
            parameter[11].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreadipUpdate", parameter);
        }
        public DataSet SpreadipList()
        {
            return DbHelper.RunProcedure("ProSpreadipSelect");
        }
    }
    /// <summary>
    /// 推广系统 密保问题
    /// </summary>
    public class SpreadQuestion
    {
        public SpreadQuestion()
        { }
        public bool SpreadQuestionAdd(MODEL.SpreadQuestion model)
        {
            SqlParameter[] parameter = { new SqlParameter("@question",SqlDbType.VarChar,200),
                                         new SqlParameter("@type",SqlDbType.Bit),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = model.SQUESTION;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.STYPE;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreadquestionAdd", parameter);
        }
        public DataSet SpreadQuestionList()
        {
            return DbHelper.RunProcedure("ProSpreadquestionSelect");
        }
    }
    /// <summary>
    /// 推广系统 推广信息主表
    /// </summary>
    public class SpreadMaster
    {
        public SpreadMaster()
        { }
        public bool SpreadMasterAdd(MODEL.SpreadMaster model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.VarChar,20),
                                         new SqlParameter("@spreadid",SqlDbType.VarChar,20),
                                         new SqlParameter("@regnum",SqlDbType.Int),
                                         new SqlParameter("@newcard",SqlDbType.Int),
                                         new SqlParameter("@year",SqlDbType.Char,4),
                                         new SqlParameter("@month",SqlDbType.Char,2),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = GetID();
            parameter[1].Value = model.SSPREADID;
            parameter[2].Value = model.SREGNUM;
            parameter[3].Value = model.SNEWCARD;
            parameter[4].Value = model.SYEAR;
            parameter[5].Value = model.SMONTH;
            for (int i = 0; i < 6; i++)
            {
                parameter[i].Direction = ParameterDirection.Input;
            }
            parameter[6].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreadmasterAdd", parameter);
        }
        public DataSet SpreadMasterList()
        {
            return DbHelper.RunProcedure("ProSpreadmasterSelect");
        }
        public DataSet SpreadMasterList(string userId)
        {
            SqlParameter[] parameter = { new SqlParameter("@userid", SqlDbType.VarChar, 20) };
            parameter[0].Value = userId;
            parameter[0].Direction = ParameterDirection.Input;
            return DbHelper.RunProcedureS("ProSpreadmasterSelectByuserID", parameter);
        }
        public string GetID()
        {
            string returnStr = string.Empty;
            string id = DateTime.Now.ToString("yyyyMMdd");
            string sqlStr = "SELECT MAX(SID) FROM G_SPREADMASTER WHERE SID LIKE '" + id + "%'";
            DataSet ds = DbHelper.Query(sqlStr);
            if (ds.Tables[0].Rows[0][0].ToString() == "null" || ds.Tables[0].Rows[0][0].ToString() == null || ds.Tables[0].Rows[0][0].ToString() == "")
            {
                returnStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "001";
            }
            else
            {
                int i = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString().Substring(17, 3));
                returnStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + string.Format("{0:D3}", i + 1);
            }
            return returnStr;
        }
    }
    /// <summary>
    /// 推广系统 推广信息副表
    /// </summary>
    public class SpreadDeputy
    {
        public SpreadDeputy()
        { }
        public bool SpreadDeputyAdd(MODEL.SpreadDeputy model)
        {
            SqlParameter[] parameter = { new SqlParameter("@id",SqlDbType.VarChar,20),
                                         new SqlParameter("@spreadid",SqlDbType.VarChar,20),
                                         new SqlParameter("@userid",SqlDbType.VarChar,12),
                                         new SqlParameter("@newcardid",SqlDbType.VarChar,50),
                                         new SqlParameter("@ip",SqlDbType.VarChar,15),
                                         new SqlParameter("@time",SqlDbType.DateTime),
                                         new SqlParameter("@year",SqlDbType.Char,4),
                                         new SqlParameter("@month",SqlDbType.Char,2),
                                         new SqlParameter("@day",SqlDbType.Char,2),
                                         new SqlParameter("@type",SqlDbType.Bit),
                                         new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = GetID();
            parameter[1].Value = model.SSPEARDID;
            parameter[2].Value = model.SUSERID;
            parameter[3].Value = model.SNEWCARDID;
            parameter[4].Value = model.SIP;
            parameter[5].Value = model.STIME;
            parameter[6].Value = model.SYEAR;
            parameter[7].Value = model.SMONTH;
            parameter[8].Value = model.SDAY;
            parameter[9].Value = model.STYPE;
            for (int i = 0; i < 10; i++)
            {
                parameter[i].Direction = ParameterDirection.Input;
            }
            parameter[10].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreaddeputyAdd", parameter);
        }
        public DataSet SpreadDeputyList()
        {
            return DbHelper.RunProcedure("ProSpreaddeputySelect");
        }
        public DataSet SpreadDeputyList(MODEL.SpreadDeputy model)
        {
            SqlParameter[] parameter ={new SqlParameter("@spreadid",SqlDbType.VarChar,20),
                                       new SqlParameter("@year",SqlDbType.Char,4),
                                       new SqlParameter("@month",SqlDbType.Char,2),
                                       new SqlParameter("@day",SqlDbType.Char,2)};
            parameter[0].Value = model.SSPEARDID;
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SYEAR;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Value = model.SMONTH;
            parameter[2].Direction = ParameterDirection.Input;
            parameter[3].Value = model.SDAY;
            parameter[3].Direction = ParameterDirection.Input;
            return DbHelper.RunProcedureS("ProSpreaddeputySelectByTime", parameter);
        }
        public bool SpreadMasterUpdate(MODEL.SpreadDeputy model)
        {
            SqlParameter[] parameter ={new SqlParameter("@id",SqlDbType.VarChar,20),
                                       new SqlParameter("@spreadid",SqlDbType.VarChar,20),
                                       new SqlParameter("@userid",SqlDbType.VarChar,12),
                                       new SqlParameter("@newcardid",SqlDbType.VarChar,50),
                                       new SqlParameter("@year",SqlDbType.Char,4),
                                       new SqlParameter("@month",SqlDbType.Char,2),
                                       new SqlParameter("@Return",SqlDbType.Int)};
            parameter[0].Value = new DAL.SpreadMaster().GetID();
            parameter[0].Direction = ParameterDirection.Input;
            parameter[1].Value = model.SSPEARDID;
            parameter[1].Direction = ParameterDirection.Input;
            parameter[2].Value = model.SUSERID;
            parameter[2].Direction = ParameterDirection.Input;
            parameter[3].Value = model.SNEWCARDID;
            parameter[3].Direction = ParameterDirection.Input;
            parameter[4].Value = model.SYEAR;
            parameter[4].Direction = ParameterDirection.Input;
            parameter[5].Value = model.SMONTH;
            parameter[5].Direction = ParameterDirection.Input;
            parameter[6].Direction = ParameterDirection.ReturnValue;
            return DbHelper.RunProcedure("ProSpreadmasterUpdate", parameter);
        }
        public string GetID()
        {
            string returnStr = string.Empty;
            string id = DateTime.Now.ToString("yyyyMMdd");
            string sqlStr = "SELECT MAX(SID) FROM G_SPREADDEPUTY WHERE SID LIKE '" + id + "%'";
            DataSet ds = DbHelper.Query(sqlStr);
            if (ds.Tables[0].Rows[0][0].ToString() == "null" || ds.Tables[0].Rows[0][0].ToString() == null || ds.Tables[0].Rows[0][0].ToString() == "")
            {
                returnStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "001";
            }
            else
            {
                int i = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString().Substring(17, 3));
                returnStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + string.Format("{0:D3}", i + 1);
            }
            return returnStr;
        }
    }
}

